package com.baizhi.wd.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.baizhi.wd.entity.Person;
import com.baizhi.wd.util.JdbcUtil;

public class PersonDaoImpl implements PersonDao{
	//1���
	public void insertPerson(Person per){
		Connection con = null;
		PreparedStatement pstm = null;
		try{
		//1 2���ù�����  ��ȡ����
		con=JdbcUtil.getCon();
		//3 ����satement
		String sql = "insert into person values(person_seq.nextval,?,?,?,?,?,?)";
		pstm = con.prepareStatement(sql);
		//4?��ֵ      ��ִ��sql
		pstm.setString(1, per.getName());
		pstm.setString(2, per.getMobile());
		pstm.setString(3, per.getTelphone());
		pstm.setString(4,per.getEmail());
		pstm.setString(5,per.getCity());
		
		//strDate-->utilDate
		java.util.Date utilDate = per.getBirthday();
		//utilDate-->sqlDate
		java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
		pstm.setDate(6,sqlDate);
		int row=pstm.executeUpdate();
		System.out.println(row+"��");
		//5�����ѯ���
		}catch(Exception e){throw new RuntimeException(e);}
		finally{
		//6�ͷ���Դ  ���ù�����
			JdbcUtil.release(null, pstm, null);
		}
	}
	
	//2ɾ��
	public void deletePerson(int id){
		Connection con = null;
		PreparedStatement pstm = null;	
		try{
		//1 2 ����
		con = JdbcUtil.getCon();
		//3 ����statement
		String sql = "delete from person where id =?";
		pstm = con.prepareStatement(sql);
		//4  ? ; ִ��sql��� 
		pstm.setInt(1, id);
		pstm.executeUpdate();
		
		//5�����ѯ
		}catch(Exception e){throw new RuntimeException(e);}
		 finally{
		//6�ͷ���Դ
		JdbcUtil.release(null, pstm, null);
		}
	}
	
	//3��һ��
	public Person selectOnePerson(int sid){
		Connection con = null;
		PreparedStatement pstm = null;
		Person per=null;
		ResultSet re = null;
		try{
		//1 2���ù�����  ��ȡ����
		con=JdbcUtil.getCon();
		//3 ����satement
		String sql = "select name,mobile,telphone,email,city,birthday from person where id = ?";
		pstm = con.prepareStatement(sql);
		//4?��ֵ      ��ִ��sql
		pstm.setInt(1, sid);
		
		re = pstm.executeQuery();
		if(re.next()){
		String name=re.getString("name");
		String mobile=re.getString("mobile");
		String telphone = re.getString("telphone");
		String email = re.getString("email");
		String city = re.getString("city");
		java.util.Date utilDate = re.getDate("birthday");
		//���ֶη�װ��һ������
		per = new Person(sid,name,mobile,telphone,email,city,utilDate);
		}
		return per;
		//5�����ѯ���
		}catch(Exception e){throw new RuntimeException(e);}
		finally{
		//6�ͷ���Դ  ���ù�����
			
			JdbcUtil.release(re, pstm, null);;
		}
		
		}
	//4��ѯ����
	public List<Person> selectAll(){
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		try{
			// 1-2 �������࣬��ȡ����
			con = JdbcUtil.getCon();
			// 3 ����pstm
			String sql = "select id,name,mobile,telphone,email,city,birthday from person";
			pstm = con.prepareStatement(sql);
			
			// 4 ִ��SQL
			rs = pstm.executeQuery();
			
			// 5 �����ѯ���r-->o��
			List<Person> list = new ArrayList<Person>();
			while(rs.next()){
				// ��ȡ�ֶ�ֵ
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String mobile = rs.getString("mobile");
				String telphone = rs.getString("telphone");
				String email = rs.getString("email");
				String city = rs.getString("city");
				java.util.Date utilDate = rs.getDate("birthday");
				// ���ֶ�ֵ  ��װ�� 1��accounts����
				Person per = new Person(id,name,mobile,telphone,email,city,utilDate);
				// �Ѷ��� --> list���ϣ�ͬ1����
				list.add(per);
			}
			return list;
		} catch(Exception e) {
			throw new RuntimeException(e);
		} finally {
			// 6 �������࣬�ͷ���Դ
			JdbcUtil.release(rs, pstm, null);
		}
	}
	//5ģ���ѯ mobile
		public List<Person> selectMobileLike(String num){
			Connection con = null;
			PreparedStatement pstm = null;
			ResultSet rs = null;
			
			try{
				// 1-2 �������࣬��ȡ����
				con = JdbcUtil.getCon();
				// 3 ����pstm
				String sql = "select id,name,mobile,telphone,email,city,birthday from person where mobile like ?";
				pstm = con.prepareStatement(sql);
				
				// 4 ��?   ;ִ��SQL
				pstm.setString(1, "%"+num+"%");
				rs = pstm.executeQuery();
				
				// 5 �����ѯ���r-->o��
				List<Person> list = new ArrayList<Person>();
				while(rs.next()){
					// ��ȡ�ֶ�ֵ
					int id = rs.getInt("id");
					String name = rs.getString("name");
					String mobile = rs.getString("mobile");
					String telphone = rs.getString("telphone");
					String email = rs.getString("email");
					String city = rs.getString("city");
					java.util.Date utilDate = rs.getDate("birthday");
					// ���ֶ�ֵ  ��װ�� 1��accounts����
					Person per = new Person(id,name,mobile,telphone,email,city,utilDate);
					// �Ѷ��� --> list���ϣ�ͬ1����
					list.add(per);
				}
				return list;
			} catch(Exception e) {
				throw new RuntimeException(e);
			} finally {
				// 6 �������࣬�ͷ���Դ
				JdbcUtil.release(rs, pstm, null);
			}	
	}
		//6ģ���ѯ name
		public List<Person> selectNameLike(String nam){
			Connection con = null;
			PreparedStatement pstm = null;
			ResultSet rs = null;
			
			try{
				// 1-2 �������࣬��ȡ����
				con = JdbcUtil.getCon();
				// 3 ����pstm
				String sql = "select id,name,mobile,telphone,email,city,birthday from person where name like ?";
				pstm = con.prepareStatement(sql);
				
				// 4 ��?   ;ִ��SQL
				pstm.setString(1, "%"+nam+"%");
				rs = pstm.executeQuery();
				
				// 5 �����ѯ���r-->o��
				List<Person> list = new ArrayList<Person>();
				while(rs.next()){
					// ��ȡ�ֶ�ֵ
					int id = rs.getInt("id");
					String name = rs.getString("name");
					String mobile = rs.getString("mobile");
					String telphone = rs.getString("telphone");
					String email = rs.getString("email");
					String city = rs.getString("city");
					java.util.Date utilDate = rs.getDate("birthday");
					// ���ֶ�ֵ  ��װ�� 1��accounts����
					Person per = new Person(id,name,mobile,telphone,email,city,utilDate);
					// �Ѷ��� --> list���ϣ�ͬ1����
					list.add(per);
				}
				return list;
			} catch(Exception e) {
				throw new RuntimeException(e);
			} finally {
				// 6 �������࣬�ͷ���Դ
				JdbcUtil.release(rs, pstm, null);
			}	
	}
		//7�޸�
		public void update(Person per){
			Connection con = null;
			PreparedStatement pstm = null;
			
			try{
				con = JdbcUtil.getCon();
				String sql = "update person set name = ?,mobile = ?,telphone=?,email = ?,city=? ,birthday=?  where id = ?";
				pstm = con.prepareStatement(sql);
				
				pstm.setString(1, per.getName());
				pstm.setString(2, per.getMobile());
				pstm.setString(3, per.getTelphone());
				pstm.setString(4, per.getEmail());
				pstm.setString(5, per.getCity());
					
				//utilDate --> sqlDate  //getTime ������
				java.util.Date utilDate = per.getBirthday();
				java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
				// ������?ռλ����ֵ
				pstm.setDate(6,sqlDate);
				pstm.setInt(7,per.getId());
				int row = pstm.executeUpdate();
				//System.out.println(row+"��");
			}catch(Exception e){throw new RuntimeException(e);}
			 finally{
				 JdbcUtil.release(null, pstm, null);
			 }
		}
			
					
}
